//------------------------------------------------------------------------------
// Bureaucratic Revolving Doors
// Kyuwon Lee & Hye Young You
// Journal of Politics 
// Do File to Replicate all the Tables and Figures
//------------------------------------------------------------------------------

********************************************************************************
* MAIN MANUSCRIPT TABLES & FIGURE
********************************************************************************

*******************************************
* TABLE 1: FIRM-LEVEL 
*******************************************
clear
use firm_level.dta 
egen firmid = group(firm)
gen lnnumreport = ln(numreport+1)
gen lnnumlobustr =ln(numlobustr+1)
gen lnnumtradeagency = ln(numtradeagency+1)
eststo clear
eststo: quietly areg anycomm      int1 i.year, a(firmid) cluster(firmid) 
eststo: quietly areg lnnumreport  int1 i.year, a(firmid) cluster(firmid) 
eststo: quietly areg lob_total    int1 i.year, a(firmid) cluster(firmid)
eststo: quietly areg lnnumlobustr int1 i.year, a(firmid) cluster(firmid) 
eststo: quietly areg lnnumtradeagency int1 i.year, a(firmid) cluster(firmid) 
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*) se ar2

* mean outcome values 
sum anycomm lnnumreport lob_total lnnumlobustr lnnumtradeagency 


*******************************************
* TABLE 2: PLACEBO EFFECTS
*******************************************
clear
use placebo.dta
egen firmid = group(firm)
gen lnnumreport = ln(numreport+1)
gen lnnumlobustr =ln(numlobustr+1)
gen lnnumtradeagency = ln(numtradeagency+1)

eststo clear
eststo: quietly areg anycomm      future i.year, a(firmid) cluster(firmid) 
eststo: quietly areg lnnumreport  future i.year, a(firmid) cluster(firmid) 
eststo: quietly areg lob_total    future i.year, a(firmid) cluster(firmid)
eststo: quietly areg lnnumlobustr future i.year, a(firmid) cluster(firmid) 
eststo: quietly areg lnnumtradeagency future i.year, a(firmid) cluster(firmid) 
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*) se ar2

* mean outcome values
sum anycomm lnnumreport lob_total lnnumlobustr lnnumtradeagency



*******************************************
* TABLE 3: An Example of USTR Bureaucrat 
*******************************************
clear
use bureau_firm_level.dta
gen connected = 0
replace connected = 1 if year > start_year 
replace categories ="legal" if categories =="law/industry"
rename categories division 
rename fyear_pay starting_salary
rename cfscore_bureau ideology 
order name firm year connected working_ustr division starting_salary jd ideology 
br name firm year connected working_ustr division starting_salary jd ideology  if name =="STRATFORD,TIMOTHY PAUL"




*******************************************
* TABLE 4: BUREAU-FIRM-YEAR LEVEL
*******************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)

eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2	

* mean outcome values 
sum anycomm numreport lob_total numlobustr numtradeagency 



*********************************************
* FIGURE 2: USTR Ideal Point During 1997-2017
*********************************************
clear
use ustr_ideology.dta
bys year: egen median_cf = median(cfscore)
bys year: egen revolver_cf = median(cfscore) if revolver == 1
sort year revolver_cf 
bys year: replace revolver_cf = revolver_cf[_n-1] if missing(revolver_cf) & _n >=1
bys year: gen tt = _n == 1

twoway (connected median_cf year if tt==1, lwidth(medthick) lpattern(dash) mcol("emerald") lcolor("emerald") graphregion(color(white))) ///
       (connected revolver_cf year if tt ==1, lwidth(medthick) lcolor("maroon") mcolor("maroon") ///
	   legend(order (1 "all" 2 "revolving door") size(small) position(1) ring(0)) ///
	    xlabel(1997(5)2017)   xtitle("Year") ytitle("Median CF Score") yscale(r(-1.5 0.5)) ylabel(-1.5(0.5)0.5))




*******************************************
* TABLE 5: HETEROGENEOUS EFFECTS 1
*******************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)

* Column (1 with JD
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if jd ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if jd ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if jd ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if jd ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if jd ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1


* Column (2) without JD 
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if jd ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if jd ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if jd ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if jd ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if jd ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1


*Sub-agency
gen expertise = 0
replace expertise = 1 if categories =="law/industry"
replace expertise = 1 if categories =="region"

* Column (3) Industry/Region
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if expertise==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if expertise ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if expertise ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if expertise ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if expertise ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1

* Column (4) PR
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if expertise==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if expertise ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if expertise ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if expertise ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if expertise ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1



*******************************************
* TABLE 6: HETEROGENEOUS EFFECTS 2 
*******************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)

* dividing salaries into high and low 
gen high = 0
replace high = 1 if fyear_pay >=105000

* Column (1) High Salary
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if high==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if high ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if high ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if high ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if high ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1

* Column (2) Low Salary
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if high==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if high ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if high ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if high ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if high ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1


* dividing ideology to aligned and non-aligned 
gen median = 0
replace median = -1.013 if inlist(year, 1997,1998,1999,2000,2003,2004,2007)
replace median = -0.978 if inlist(year, 2001,2005)
replace median = -0.895 if year ==2002
replace median = -0.9955 if year ==2006
replace median = -1.197 if year ==2008
replace median = -1.316 if year ==2009
replace median = -1.3405 if year ==2010
replace median = -1.4035 if year ==2011
replace median = -1.4095 if inlist(year, 2012,2013,2015,2016)
replace median = -1.4215 if year ==2014
replace median = -1.445 if year ==2017

gen pivot = abs(cfscore_bureau - median)
gen pivot_bin1 =0 if pivot!=.
replace pivot_bin1 =1 if pivot <= 0.545

* Column (3) Aligned
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if pivot_bin1==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
 
* Column (4) Not Aligned
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if pivot_bin1==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1





********************************************************************************
* APPENDIX TABLES & FIGURES 
********************************************************************************

*******************************************
* TABLE A1: Revolving Door Bureaucrats
*******************************************
clear
use career.dta
gen lob = 0
replace lob = 1 if lobbyistid!=""
gen gov = 0
replace gov = 1 if employer_type =="CONGRESS"
replace gov = 1 if employer_type =="FEDERAL GOVT"
replace gov = 1 if employer_type =="STATE/LOCAL GOVT"
bys name agency: egen fyear = min(start_year)
gen fgov = 1 if start_year ==fyear & gov == 1 
replace fgov = 0 if fgov ==.
bys name agency: egen govfirst = max(fgov)
gen fprivate = 1 if govfirst == 0
replace fprivate = 0 if govfirst == 1
* government => private sector => government 
gen backtogov = 1 if govfirst == 1 & start_year != fyear  & gov ==1
replace backtogov = 0 if backtogov ==.
bys name: egen maxbacktogov = max(backtogov)
* private sector => government => prviate sector
gen backtoprivate = 1 if govfirst == 0 & start_year !=fyear & gov == 0 
replace backtoprivate = 0 if backtoprivate == .
bys name: egen maxbacktoprivate = max(backtoprivate)

* career type
gen exec = 0
replace exec = 1 if employer_type =="FEDERAL GOVT"
gen cong = 0
replace cong = 1 if employer_type =="CONGRESS"
gen lobbying = 0
replace lobbying = 1 if employer_type =="LOBBYING FIRM"
gen private = 0
replace private = 1 if employer_type =="PRIVATE SECTOR"
gen locgov = 0
replace locgov = 1 if employer_type =="STATE/LOCAL GOVT"
gen other = 0
replace other = 1 if employer_type =="CAMPAIGN" | employer_type =="OTHER"
bys name agency: egen maxexec = max(exec)
bys name agency: egen maxcong = max(cong)
bys name agency: egen maxlob = max(lobbying)
bys name agency: egen maxprivate = max(private)
bys name agency: egen maxloc = max(locgov)
bys name agency: egen maxoth = max(other)

bys name agency: gen tt = 1 if _n ==1  
rename govfirst start_in_gov
rename fprivate start_in_private
rename maxbacktogov gov_private_gov
rename maxbacktoprivate private_gov_private 
rename maxexec executive_branch
rename maxcong congress
rename maxloc state_local_gov
rename maxlob lobbyingfirm
rename maxprivate private_sector 


* PANEL A: Career Trajectory
sum start_in_gov start_in_private gov_private_gov private_gov_private if tt==1

* PANEL B: Career Experience
sum executive_branch congress state_local_gov lobbyingfirm private_sector if tt == 1 



*******************************************
* TABLE A2: Summary Stats at Firm-level
*******************************************
clear
use firm_level.dta
gen lnnumreport = ln(numreport+1)
gen lnnumlobustr =ln(numlobustr+1)
gen lnnumtradeagency = ln(numtradeagency+1)

tabstat anycomm lob_total lnnumreport lnnumlobustr lnnumtradeagency, stat(mean median sd min max n) col(stat)


***********************************************
* TABLE A3: Summary Stats at Firm-Bureau level
***********************************************
clear
use bureau_firm_level.dta
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen lnnumreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
gen lnnumlobustr = ln(numlobustr +1)
gen lnnumtradeagency = ln(numtradeagency +1)
gen median = 0
replace median = -1.013 if inlist(year, 1997,1998,1999,2000,2003,2004,2007)
replace median = -0.978 if inlist(year, 2001,2005)
replace median = -0.895 if year ==2002
replace median = -0.9955 if year ==2006
replace median = -1.197 if year ==2008
replace median = -1.316 if year ==2009
replace median = -1.3405 if year ==2010
replace median = -1.4035 if year ==2011
replace median = -1.4095 if inlist(year, 2012,2013,2015,2016)
replace median = -1.4215 if year ==2014
replace median = -1.445 if year ==2017
gen ideo_gap = abs(cfscore_bureau - median)

tabstat anycomm lob_total lnnumreport lnnumlobustr lnnumtradeagency fyear_pay ideo_gap, stat(mean median sd min max n) col(stat)



******************************************************************
* FIGURE A1: Distribution of Revolving-door Bureaucrats' CF Scores
******************************************************************
clear
use bureau_firm_level.dta
bys pseudo_id: gen tt = _n == 1 
gen cfyes = 0
replace cfyes = 1 if cfscore_bureau != .
tab cfyes if tt ==1 
gen pr = 0
replace pr = 1 if categories =="PR"
bys pseudo_id: egen firstgrade = min(paygrade)

twoway (hist cfscore_bureau if tt ==1, bin(20) color(teal) xtitle("Revolving-Door Bureaucrats' CF Scores") ///
	   fcolor(teal) lcolor(black) plotregion(style(none)) graphregion(color(white)))

	   
***********************************************************************
* TABLE A4: Descriptive Stats on Bureaucrats with and without CF Scores
***********************************************************************
clear
use bureau_firm_level.dta
bys pseudo_id: gen tt = _n == 1 
gen cfyes = 0
replace cfyes = 1 if cfscore_bureau != .
tab cfyes if tt ==1 
gen pr = 0
replace pr = 1 if categories =="PR"
bys pseudo_id: egen firstgrade = min(paygrade)
rename firstgrade starting_gs_rank
rename fyear_pay starting_salary
rename pr pr_division

tabstat starting_gs_rank starting_salary pr_division jd if cfyes ==1 & tt ==1, stat(mean sd) col(stat)
tabstat starting_gs_rank starting_salary pr_division jd if cfyes ==0 & tt ==1, stat(mean sd) col(stat)

ttest starting_gs_rank if tt ==1, by(cfyes)
ttest starting_salary if tt ==1, by(cfyes)
ttest pr_division if tt ==1, by(cfyes)
ttest jd if tt ==1, by(cfyes)



*********************************************
* FIGURE A2: Distribution of Firms' CF Scores
*********************************************
clear
use bureau_firm_level.dta
bys firm: gen ff = _n == 1

twoway (hist cfscore_firm if ff ==1, bin(20) color(teal) xtitle("Firms' CF Score") ///
	   fcolor(teal) lcolor(black) plotregion(style(none)) graphregion(color(white)))


	   
********************************************************************************
* FIGURE A3: Distribution of Difference in CF Scores between Bureaucrat and USTR
********************************************************************************
clear
use bureau_firm_level.dta
gen median = 0
replace median = -1.013 if inlist(year, 1997,1998,1999,2000,2003,2004,2007)
replace median = -0.978 if inlist(year, 2001,2005)
replace median = -0.895 if year ==2002
replace median = -0.9955 if year ==2006
replace median = -1.197 if year ==2008
replace median = -1.316 if year ==2009
replace median = -1.3405 if year ==2010
replace median = -1.4035 if year ==2011
replace median = -1.4095 if inlist(year, 2012,2013,2015,2016)
replace median = -1.4215 if year ==2014
replace median = -1.445 if year ==2017

gen pivot = abs(cfscore_bureau - median)

twoway (hist pivot, bin(20) color(teal) xtitle("|Bureaucrat CF Score - USTR Median CF Score|") ///
	   fcolor(teal) lcolor(gs5) plotregion(style(none)) graphregion(color(white)))



************************************************************************************
* FIGURE A4; Distribution of First Quarter of All USTR Bureacurat's First Pay Period
************************************************************************************
clear 
use opm_ustr.dta
tostring(date), gen(date2)
order date2, after(date)
gen year = substr(date2, 1, 4)
order year, after(date2)
gen month = substr(date2, 5,2)
order month, after(year)
destring(month), replace 
destring(year), replace
bys pseudo_id: egen fyear = min(year)
bys pseudo_id: gen tt = _n == 1
drop if fyear == 1977 
bys pseudo_id: egen fdate = min(date)
format fdate %13.0f
sort fdate  
br fdate if tt ==1 
tostring(fdate), gen(date3)
gen year2 = substr(date3, 1, 4)
gen month3 = substr(date3, 5,2)
destring(year2), replace
destring(month3), replace 
gen month4 =. 
replace month4 = 0.25 if month3 == 3
replace month4 = 0.5  if month3 == 6
replace month4 = 0.75 if month3 == 9
replace month4 = 1 if month3 == 12
gen time = year2 + month4

twoway (hist time if tt ==1, bin(140) freq xtitle("Timing of Joining the USTR") ytitle("Number of Bureaucrat") ///
	   fcolor(gs5) lcolor(gray) plotregion(style(none)) graphregion(color(white)) ysize(3) xlab(1980(5)2010))




******************************************************************************************
* FIGURE A5; Distribution of First Quarter of Revolving Door Bureacurat's First Pay Period
******************************************************************************************
clear
use opm_ustr_revolving.dta 
twoway (hist time, bin(74) freq xtitle("Timing of Joining the USTR") ytitle("Number of Bureaucrat") ///
	   fcolor(gs5) lcolor(gray) plotregion(style(none)) graphregion(color(white)) ysize(3) xlab(1980(5)2010))




****************************************************
* TABLE B1: Jurisdiction of USTR Advisory Committees
****************************************************
clear
use advisory_comm.dta
keep AgencyAbbr CommitteeName FY
duplicates drop 
destring(FY), replace
rename FY year
rename AgencyAbbr agency 
bys agency year: egen numcommittee = count(year)
keep agency year numcommittee
duplicates drop 
drop if year == 1997
gen agencyid =.
replace agencyid = 1 if agency =="DOC"
replace agencyid = 2 if agency =="USDA"
replace agencyid = 3 if agency =="USTR"
reshape wide numcommittee agency, i(year) j(agencyid)
gen total = numcommittee1 + numcommittee2 + numcommittee3 
br 



****************************************************
* TABLE C1: Number of USTR Officials by Year
****************************************************
clear
use ustr_by_year.dta
tab year 



****************************************************
* FIGURE C1: Number of USTR Officials by Year
****************************************************
clear
use ustr_by_year.dta
bys year: egen numbureau = count(year)
bys year: gen tt = _n == 1
twoway (connected numbureau year if tt==1, lwidth(medthick) graphregion(color(white)) ///
	    xlabel(1997(2)2017)   xtitle("Year") ytitle("Number of Bureaucrat") yscale(r(100 300)) ylabel(100(50)300))


****************************************************
* TABLE C2: Descriptive Stats on the OPM Data
****************************************************
clear
use ustr_by_career_info.dta
rename startedu edu_level
rename startgrade gs_rank
tabstat edu_level gs_rank startsalary if info_found ==1, stat(mean sd) col(stat)
tabstat edu_level gs_rank startsalary if info_found ==0, stat(mean sd) col(stat)

ttest edu_level, by(info_found)
ttest gs_rank, by(info_found)
ttest startsalary, by(info_found)


******************************************************
* FIGURE C2: Distribution of Starting Year in the USTR
******************************************************
clear
use ustr_by_career_info.dta 
twoway (hist firstyear_opm if info_found==0, color(teal)) ///
       (hist firstyear_opm if info_found==1, xtitle("First year in the USTR") ///
	   fcolor(none) lcolor(black) plotregion(style(none)) graphregion(color(white))), /// 
	   legend(order(1 "without career info" 2 "with career info")  size(small) position(11) ring(0) col(1))




*************************************************************
* TABLE C3: Types of Previous Employers of the USTR Revolvers
*************************************************************
clear
use ustr_career.dta
tab employer_type, sort 




******************************************************************
* TABLE E1: Effect of Direct Connection on Political Participation
******************************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)

eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if direct==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if direct ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if direct ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if direct ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if direct ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2


********************************************************************
* TABLE E2: Effect of Indirect Connection on Political Participation
********************************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)

eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if direct==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if direct ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if direct ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if direct ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year if direct ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2



**********************************************************************************
* TABLE E3: Fffect of Alignment with the Administration on Political Participation
**********************************************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)
   
gen firmdem = .
replace firmdem = 1 if cfscore_firm <=-0.053 & cfscore_firm !=.
replace firmdem = 0 if firmdem ==. & cfscore_firm !=.
gen firmrep = 1 if cfscore_firm >=.462 & cfscore_firm !=.
replace firmrep = 0 if cfscore_firm ==.&  cfscore_firm !=. 

gen presdem = 0
replace presdem =1 if year < 2001
replace presdem = 1 if year > 2008 & year < 2017
gen align = .
replace align = 1 if firmdem ==1 & presdem ==1
replace align = 1 if firmrep ==0 & presdem ==0
replace align = 0 if firmdem ==1 & presdem ==0
replace align = 0 if firmrep ==0 & presdem ==1
replace align = 0 if align ==. & cfscore_firm !=.

eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 align i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg adv_comm    c.lag_ustr c.connected c.int1 align i.year, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 align i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 align i.year, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 align  i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2	



***********************************************************
* TABLE E4: Using Alternative Lobbying Measures as Outcomes
***********************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numagency_ustr = ln(numagency_ustr +1)
replace numtradeagency = ln(numtradeagency +1)

eststo clear
eststo: quietly areg anyTRD     c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numagency_ustr c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numtradeagency  c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2	

* mean outcome values 
sum anyTRD numagency_ustr numtradeagency 



********************************************************************************
* TABLE E5: Replication of Main Result for Bureaucrats with Complete Information
********************************************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)
keep if cfscore_bureau != . 

eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg adv_comm    c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2	



********************************************************************************
* TABLE E6: Does the substitution effect decay over time?
********************************************************************************
clear
use decay.dta 
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lag_ustr2 = working_ustr[_n-2]
bys pseudo_id: gen lag_ustr3 = working_ustr[_n-3]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
replace lag_ustr2 = 0 if ustr_startyear > 1995 & lag_ustr2 ==.
replace lag_ustr2 = 1 if ustr_startyear <=1995 & lag_ustr2==. & ustr_lastyear >= 1995
replace lag_ustr3 = 0 if ustr_startyear > 1994 & lag_ustr3 ==.
replace lag_ustr3 = 1 if ustr_startyear <=1994 & lag_ustr3==. & ustr_lastyear >= 1994
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anyDOC = 0
replace anyDOC = 1 if numcomm_DOC > 0
gen anyUSDA = 0
replace anyUSDA = 1 if numcomm_USDA > 0
gen anyUSTR = 0
replace anyUSTR = 1 if numcomm_USTR > 0
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected 
gen int2 = lag_ustr2*connected
gen int3 = lag_ustr3*connected
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)

* Panel A: lagged t-1 
eststo clear
eststo: quietly areg anycomm   c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg numreport c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr c.lag_ustr c.connected c.int1 i.year, a(id) cluster(firmid) 
lincom lag_ustr + int1

* Panel B: lagged t-2
eststo clear
eststo: quietly areg anycomm   c.lag_ustr2 c.connected c.int2 i.year, a(id) cluster(firmid) 
lincom lag_ustr2 + int2
eststo: quietly areg numreport c.lag_ustr2 c.connected c.int2 i.year, a(id) cluster(firmid) 
lincom lag_ustr2 + int2
eststo: quietly areg lob_total c.lag_ustr2 c.connected c.int2 i.year, a(id) cluster(firmid)
lincom lag_ustr2 + int2
eststo: quietly areg numlobustr c.lag_ustr2 c.connected c.int2 i.year, a(id) cluster(firmid) 
lincom lag_ustr2 + int2	

* Panel C: lagged t-3
eststo clear
eststo: quietly areg anycomm   c.lag_ustr3 c.connected c.int3 i.year, a(id) cluster(firmid) 
lincom lag_ustr3 + int3
eststo: quietly areg numreport c.lag_ustr3 c.connected c.int3 i.year, a(id) cluster(firmid) 
lincom lag_ustr3 + int3
eststo: quietly areg lob_total c.lag_ustr3 c.connected c.int3 i.year, a(id) cluster(firmid)
lincom lag_ustr3 + int3
eststo: quietly areg numlobustr c.lag_ustr3 c.connected c.int3 i.year, a(id) cluster(firmid) 
lincom lag_ustr3 + int3



***************************************************************
* TABLE E7: Dividing Bureaucrats Based on Their Work Experience
***************************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
gen work_year = end_year - start_year + 1
replace work_year = totyear if totyear!=.

replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)

gen group = .
replace group = 1 if work_year <=3 & work_year !=. 
replace group = 2 if work_year > 3 & work_year !=. 

* Panel A: Wor Years <= 3 Years
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if group ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1 // Effect of entry when connection == 1 
eststo: quietly areg adv_comm    c.lag_ustr c.connected c.int1 i.year if group ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if group ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if group ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if group ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2

* Panel B: Work Years > 3 Years 
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if group ==2, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg adv_comm    c.lag_ustr c.connected c.int1 i.year if group ==2, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if group ==2, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if group ==2, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if group ==2, a(id) cluster(firmid) 
lincom lag_ustr + int1
esttab, star(* 0.10 ** 0.05 *** 0.01) drop(*year*)se ar2





***************************************************************************
* TABLE E8: Using Different Thresholds to Determine Aligned vs. Non-aligned 
***************************************************************************
clear
use bureau_firm_level.dta
egen id = group(pseudo_id firm) 
sort pseudo_id firm year
bys pseudo_id: gen lag_ustr = working_ustr[_n-1]
bys pseudo_id: gen lead_ustr = working_ustr[_n+1]
replace lag_ustr = 0 if ustr_startyear > 1996 & lag_ustr ==.
replace lag_ustr = 1 if ustr_startyear <=1996 & lag_ustr==. & ustr_lastyear >= 1996
gen connected = 0
replace connected = 1 if year > start_year 
sort pseudo_id firm year
gen adv_comm = numcomm_DOC + numcomm_USDA + numcomm_USTR
gen anycomm = 0 
replace anycomm = 1 if adv_comm > 0
gen int1 = lag_ustr*connected // work USTR X connection 
egen firmid = group(firm)
replace numreport = ln(numreport +1)
gen lob_total = ln(contract_amt + inhouse_amt +1)
replace numlobustr = ln(numlobustr +1)
replace numtradeagency = ln(numtradeagency +1)

gen median = 0
replace median = -1.013 if inlist(year, 1997,1998,1999,2000,2003,2004,2007)
replace median = -0.978 if inlist(year, 2001,2005)
replace median = -0.895 if year ==2002
replace median = -0.9955 if year ==2006
replace median = -1.197 if year ==2008
replace median = -1.316 if year ==2009
replace median = -1.3405 if year ==2010
replace median = -1.4035 if year ==2011
replace median = -1.4095 if inlist(year, 2012,2013,2015,2016)
replace median = -1.4215 if year ==2014
replace median = -1.445 if year ==2017

gen pivot = abs(cfscore_bureau - median)

gen pivot_bin1 =0 if pivot!=.
replace pivot_bin1 =1 if pivot <= 0.94 // alternative threshould (mean value)

* Aligned 
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if pivot_bin1==1, a(id) cluster(firmid) 
lincom lag_ustr + int1 
eststo: quietly areg adv_comm    c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==1, a(id) cluster(firmid) 
lincom lag_ustr + int1

* Not-Aligned 
eststo clear
eststo: quietly areg anycomm     c.lag_ustr c.connected c.int1 i.year if pivot_bin1==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg adv_comm    c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numreport   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1
eststo: quietly areg lob_total   c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid)
lincom lag_ustr + int1
eststo: quietly areg numlobustr  c.lag_ustr c.connected c.int1 i.year if pivot_bin1 ==0, a(id) cluster(firmid) 
lincom lag_ustr + int1



